#!/usr/bin/perl -w

###############################################################################
# Load the processed geno file into the geno table in the database
#
# Parameters needed: dbname, username, temp_dir
# Assumptions: $TEMP_DIR/geno.out exists
#
# Author: Herodotos Herodotou and Nedyalko Borisov
# Date: March 11, 2010
#
##############################################################################

use getOpts qw(get_options println);

if ($#ARGV != 0)
{
   println qq(Please specify the .properties file);
   println qq(Usage: perl $0 load.properties);
   exit(-1);
}

# Get input parameters
%opt = get_options($ARGV[0]);
$DB_NAME  =$opt{'dbname'};
$USERNAME =$opt{'username'};
$TEMP_DIR = $opt{'temp_dir'};


# Get all the data files
my @files;
opendir(DIR, $TEMP_DIR) or die "Unable to open $TEMP_DIR $!";
while ($file = readdir(DIR)) {
    if($file =~ /^geno_\d+\.out$/) {
        push(@files, $file);
    }
}
closedir DIR;

# Load the data for each child table
foreach $table (@files) {
   $table =~ s/\.out//;
   println qq(Loading geno data for $table);

   # Check if the child table already exists   
   !system qq(psql -U $USERNAME -d $DB_NAME -t -o $TEMP_DIR/geno_exists ).
           qq(-c "SELECT relname FROM pg_class WHERE relname='$table'") or die $!;
   open FILE, "<", "$TEMP_DIR/geno_exists" or die $!;
   $line = <FILE>;
   close FILE;
   !system qq(rm -f $TEMP_DIR/geno_exists) or die $!;
   
   if ($line =~ /^\w*$/)
   {
      # The table does not exist -> create
      !system qq(psql -U $USERNAME -d $DB_NAME -c ).
              qq(" set client_min_messages = ERROR; CREATE TABLE $table () INHERITS (geno)" > /dev/null) or die $!;
   }
   else
   {
      # The table exists -> drop all constraints
      $drop_script = qq($TEMP_DIR/drop_$table.sql);
      open OUTFILE, ">", $drop_script or die $!;
      print OUTFILE qq(set client_min_messages = ERROR;\n);
      print OUTFILE qq(ALTER TABLE $table DROP CONSTRAINT $table\_anno_id_fkey;\n);
      print OUTFILE qq(ALTER TABLE $table DROP CONSTRAINT $table\_geno_pkey;\n);
      print OUTFILE qq(ALTER TABLE $table DROP CONSTRAINT $table\_snpval_values;\n);
      close OUTFILE;

      !system qq(psql -U $USERNAME -d $DB_NAME -f $drop_script > /dev/null) or die $!;
      !system qq(rm -f $drop_script) or die $!;
   }

   # Load the data
   !system qq(psql -U $USERNAME -d $DB_NAME -c ).
           qq("\\COPY $table FROM '$TEMP_DIR/$table.out' WITH DELIMITER AS ','") or die $!;


   # Add the constraints
   $load_script = qq($TEMP_DIR/load_$table.sql);
   open  OUTFILE, ">", $load_script or die $!;
   print OUTFILE qq(set client_min_messages = ERROR;\n);
   print OUTFILE qq(ALTER TABLE $table ADD CONSTRAINT $table\_pkey PRIMARY KEY (anno_id);\n);
   print OUTFILE qq(ALTER TABLE $table ADD CONSTRAINT $table\_anno_id_fkey\n);
   print OUTFILE qq(FOREIGN KEY (anno_id) REFERENCES anno (anno_id)\n);
   print OUTFILE qq(ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;\n);
   print OUTFILE qq(ALTER TABLE $table ADD CONSTRAINT $table\_snpval_values CHECK (snpval IN (0, 1, 2, -9));\n);
   print OUTFILE qq(ANALYZE $table;\n);
   close OUTFILE;

   !system qq(psql -U $USERNAME -d $DB_NAME -f $load_script > /dev/null) or die $!;
   !system qq(rm -f $load_script) or die $!;
   
   # Remove the out file
   !system qq(rm -f $TEMP_DIR/$table.out) or die $!;
}

println qq(Completed loading geno data);


